<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace API;
class KeyStore extends \App\SessionObject {
    /**
     * put(request, response) - put/store a text-blob (e.g. a JSON-serialized Javascript object) to the key-value store
     *     although this is designed to be a general-purpose interface to a key-value store, there are several special features added specifically to support type 'grid'
     *     (1) if 'label' supplied, the key is modified (grid keys have the form {database}.{table} or {database}.{table}.{viewlabelstr})
     *     (2) syntax checking is done on the optional SQL statement embedded in the data before it is stored
     * @param string  type       [IN] - (required) a string defining the domain of things stored (keys are unique for each type) (type 'grid' is special for table views)
     * @param string  key        [IN] - (required) the key, making the record unique within a 'type'
     * @param string  data       [IN] - the text-blob (if type is 'grid', the data is json-decoded to look for a SQL statement that is executed to verify syntax before the blob is saved)
     *                                  if data is a PHP array, it is JSON-encoded for storage and JSON-decoded when it is retrieved
     * @param string  label      [IN] - (optional) stores a label along with the data. (also, if key is of the form x.y or x.y.z, it creates a modified key x.y.z with the label as the z part)
     * @param boolean success    [OUT] - whether the call succeeded
     * @param string  message    [OUT] - message about what happened (whether success or failure)
     * @param string  key        [OUT] - the resulting key under which the data was stored (only different from input key if label is supplied)
     */
    public function put ($request, &$response) {
        global $trace, $context, $options;
        if ($trace) trace_entry();

        $type  = isset($request['type'])  ? $request['type']  : null;
        $key   = isset($request['key'])   ? $request['key']   : null;
        $label = isset($request['label']) ? $request['label'] : null;
        $data  = isset($request['data'])  ? $request['data']  : null;
        if (! isset($type)) throw new \Exception('ERROR: the "type" parameter must be supplied');
        if (! isset($key))  throw new \Exception('ERROR: the "key" parameter must be supplied');
        if (! preg_match('/^[A-Za-z0-9_]+$/', $type)) throw new \Exception('ERROR: the "type" parameter has illegal characters (type may only be the [A-Za-z0-9_] characters)');
        if (! preg_match('/^[ -&\\(-\\[\\]-~]+$/', $key)) throw new \Exception('ERROR: the "key" parameter has illegal characters (key may only be the printable ASCII characters excluding [\'"\\])');
        if (! isset($data)) throw new \Exception('ERROR: the "data" parameter must be supplied');

        $group_id = $context->getAuthenticatedGroupId();
        
        if ($type === 'grid') {
            $table        = 'db_view';
            $id_column    = 'view_id';
            $key_column   = 'view_key';
            $label_column = 'view_label';
            $group_column = 'db_owner_group_id';
            if (preg_match('/^([A-Za-z0-9_]+)\\./', $key, $matches)) {
                $db_name = $matches[1];
                $db = $context->database($db_name);
                $group_id = $db->db_owner_group_id;
            }
        }
        elseif ($type === 'chart') {
            $table        = 'db_chart';
            $id_column    = 'chart_id';
            $key_column   = 'chart_key';
            $label_column = 'chart_label';
            $group_column = 'db_owner_group_id';
            if (preg_match('/^([A-Za-z0-9_]+)\\./', $key, $matches)) {
                $db_name = $matches[1];
                $db = $context->database($db_name);
                $group_id = $db->db_owner_group_id;
            }
        }
        else {
            $table        = 'app_store';
            $id_column    = 'store_id';
            $key_column   = 'store_key';
            $type_column  = 'store_type';
            $label_column = 'content_str';
            $group_column = 'owner_group_id';
        }

        $response = array('success'=>true);   # assume we are going to succeed, whatever it is we are doing

        $matches = array();

        $metadb = $context->database("meta");

        $serializer = '';
        if (is_array($data)) {
            $serializer = 'json';
            $data = json_encode($data);
        }

        if (isset($label) && $label && $type === 'grid') {
            $view_label_str = strtolower($label);
            $view_label_str = preg_replace('/[^a-z0-9]+/','_',$view_label_str);
            if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)\\.(.+)$/', $key, $matches)) {
                $key = "$matches[1].$matches[2].$view_label_str";
            }
            elseif (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $key, $matches)) {
                $key = "$key.$view_label_str";
            }
            $update_columns = array('serializer', 'data', $label_column);
            $update_values  = array($serializer, $data, $label);
            if ($table === 'app_store') {
                $insert_columns = array($type_column, $key_column, 'serializer', 'data', $label_column, $group_column);
                $insert_values  = array($type, $key, $serializer, $data, $label, $group_id);
                $update_params  = array("$type_column-eq"=>$type, "$key_column-eq"=>$key);
            }
            else {
                $insert_columns = array($key_column, 'serializer', 'data', $label_column, $group_column);
                $insert_values  = array($key, $serializer, $data, $label, $group_id);
                $update_params  = array("$key_column-eq"=>$key);
            }
        }
        elseif (isset($label) && $label) {
            $update_columns = array('serializer', 'data', $label_column);
            $update_values  = array($serializer, $data, $label);
            if ($table === 'app_store') {
                $insert_columns = array($type_column, $key_column, 'serializer', 'data', $label_column, $group_column);
                $insert_values  = array($type, $key, $serializer, $data, $label, $group_id);
                $update_params  = array("$type_column-eq"=>$type, "$key_column-eq"=>$key);
            }
            else {
                $insert_columns = array($key_column, 'serializer', 'data', $label_column, $group_column);
                $insert_values  = array($key, $serializer, $data, $label, $group_id);
                $update_params  = array("$key_column-eq"=>$key);
            }
        }
        else {  # If no label, not a "Save As". Leave the $key alone. (unchanged)
            $update_columns = array('serializer', 'data');
            $update_values  = array($serializer, $data);
            if ($table === 'app_store') {
                $insert_columns = array($type_column, $key_column, 'serializer', 'data', $group_column);
                $insert_values  = array($type, $key, $serializer, $data, $group_id);
                $update_params  = array("$type_column-eq"=>$type, "$key_column-eq"=>$key);
            }
            else {
                $insert_columns = array($key_column, 'serializer', 'data', $group_column);
                $insert_values  = array($key, $serializer, $data, $group_id);
                $update_params  = array("$key_column-eq"=>$key);
            }
        }
        $response['key'] = $key;

        if ($type === 'grid') {
            $data = str_replace('\\"','"',$data);
            $griddata = json_decode($data, true);
            if (isset($griddata) && isset($griddata['sql'])) {
                $sql = $griddata['sql'];
                if (!preg_match('/^ *select/i', $sql))
                    throw new \Exception('ERROR: saved SQL must begin with the "select" keyword');
                if (preg_match('/^([A-Za-z0-9_]+)/', $key, $matches)) {
                    $database = $matches[1];
                    $context->database($database)->get_rows($sql);  # verify that the SQL works before it gets saved
                }
            }
        }

        $nrows = $metadb->update($table, $update_params, $update_columns, $update_values);
        if ($nrows == 0) {
            $nrows = $metadb->insert($table, $insert_columns, $insert_values);
            $response['message'] = "$nrows row(s) inserted";
        }
        else {
            $response['message'] = "$nrows row(s) updated";
        }

        if ($trace) trace_exit();
    }

    /**
     * get(request, response) - get/retrieve a text-blob (e.g. a JSON-serialized Javascript object) from the key-value store
     * @param string  type       [IN] - (required) a string defining the domain of things stored (keys are unique for each type) (type 'grid' is special for table views)
     * @param string  key        [IN] - (required) the key, making the record unique within a 'type'
     * @param boolean success    [OUT] - whether the call succeeded
     * @param string  data       [OUT] - the text-blob
     */
    public function get ($request, &$response) {
        global $trace, $context, $options;
        if ($trace) trace_entry();

        $type     = isset($request['type']) ? $request['type'] : null;
        $key      = isset($request['key'])  ? $request['key']  : null;
        $data     = isset($request['data']) ? $request['data'] : null;
        if (!$type) throw new \Exception('ERROR: the "type" parameter was not supplied');
        if (! preg_match('/^[A-Za-z0-9_]+$/', $type)) throw new \Exception('ERROR: the "type" parameter has illegal characters (type may only be the [A-Za-z0-9_] characters)');
        if (! preg_match('/^[ -&\\(-\\[\\]-~]+$/', $key)) throw new \Exception('ERROR: the "key" parameter has illegal characters (key may only be the printable ASCII characters excluding [\'"\\])');
        $response = array('success'=>true);   # assume we are going to succeed, whatever it is we are doing

        if ($type === 'grid') {
            $table        = 'db_view';
            $id_column    = 'view_id';
            $key_column   = 'view_key';
            $label_column = 'view_label';
        }
        elseif ($type === 'chart') {
            $table        = 'db_chart';
            $id_column    = 'chart_id';
            $key_column   = 'chart_key';
            $label_column = 'chart_label';
        }
        else {
            $table        = 'app_store';
            $id_column    = 'store_id';
            $key_column   = 'store_key';
            $type_column  = 'store_type';
            $label_column = 'content_str';
        }
        if ($table === 'app_store') {
            $select_params = array('store_type-eq'=>$type, 'store_key-eq'=>$key);
        }
        else {
            $select_params = array("$key_column-eq"=>$key);
        }

        $metadb = $context->database("meta");

        list($serializer, $label, $data) = $metadb->get_row($table, $select_params, array('serializer',$label_column,'data'));
        if (isset($serializer)) {
            if ($serializer === 'json') {
                $data = json_decode($data, true);
            }
        }
        if (isset($label) && $label) { $response['label'] = $label; }
        $response['data'] = $data;

        if ($trace) trace_exit();
    }

    /**
     * listEntries(request, response) - list/retrieve a set of text-blobs (e.g. a JSON-serialized Javascript object) from the key-value store
     * @param string  type         [IN] - (required) a string defining the domain of things stored (keys are unique for each type) (type 'grid' is special for table views)
     * @param string  key          [IN] - (required) the key pattern (e.g. '*', 'reference.*', 'reference.geo_country.*')
     * @param integer include_data [IN] - (optional) 0=do not return the (potentially large) data structures. 1=return the data structures too
     * @param boolean success      [OUT] - whether the call succeeded
     * @param string  data         [OUT] - an array of objects with 'key' and 'value' attributes
     */
    public function listEntries ($request, &$response) {
        global $trace, $context, $options;
        if ($trace) trace_entry();

        $type     = isset($request['type']) ? $request['type'] : null;
        $key      = isset($request['key'])  ? $request['key']  : null;
        $data     = isset($request['data']) ? $request['data'] : null;
        if (! preg_match('/^[A-Za-z0-9_]+$/', $type)) throw new \Exception('ERROR: the "type" parameter has illegal characters (type may only be the [A-Za-z0-9_] characters)');
        if (! preg_match('/^[ -&\\(-\\[\\]-~]+$/', $key)) throw new \Exception('ERROR: the "key" parameter has illegal characters (key may only be the printable ASCII characters excluding [\'"\\])');
        $response = array('success'=>true);   # assume we are going to succeed, whatever it is we are doing

        if ($type === 'grid') {
            $table        = 'db_view';
            $id_column    = 'view_id';
            $key_column   = 'view_key';
            $label_column = 'view_label';
        }
        elseif ($type === 'chart') {
            $table        = 'db_view';
            $id_column    = 'chart_id';
            $key_column   = 'chart_key';
            $label_column = 'chart_label';
        }
        else {
            $table        = 'app_store';
            $id_column    = 'store_id';
            $key_column   = 'store_key';
            $type_column  = 'store_type';
            $label_column = 'content_str';
        }

        if ($table === 'app_store') {
            $select_params = array('store_type-eq'=>$type, 'store_key-contains'=>$key);
            $columns = array('store_type','store_key','serializer','content_str');
        }
        else {
            $select_params = array("$key_column-contains"=>$key);
            $columns = array($key_column,'serializer',$label_column);
        }

        $metadb = $context->database("meta");

        if (isset($request['include_data']) && $request['include_data'] && $request['include_data'] !== 'false') {
            $columns[] = 'data';
        }
        $rows = $metadb->get_hashes($table, $select_params, $columns);
        for ($i = 0; $i < count($rows); $i++) {
            if (isset($rows[$i]['data'])) {
                $serializer = isset($rows[$i]['serializer']) ? $rows[$i]['serializer'] : '';
                if (!$serializer) {
                    if ($rows[$i]['data'][0] === '{' || $rows[$i]['data'][0] === '[') {
                        $serializer = 'json';
                    }
                }
                if ($serializer === 'json') {
                    $rows[$i]['data'] = json_decode($rows[$i]['data'], true);
                }
            }
            if (isset($rows[$i][$label_column])) {
                $rows[$i]['label'] = $rows[$i][$label_column];
                unset($rows[$i][$label_column]);
            }
        }
        
        $response['data'] = $rows;

        if ($trace) trace_exit();
    }

    /**
     * delete(request, response) - delete/remove a text-blob (e.g. a JSON-serialized Javascript object) from the key-value store
     * @param string  type       [IN] - (required) a string defining the domain of things stored (keys are unique for each type) (type 'grid' is special for table views)
     * @param string  key        [IN] - (required) the key, making the record unique within a 'type'
     * @param boolean success    [OUT] - whether the call succeeded
     */
    public function delete ($request, &$response) {
        global $trace, $context, $options;
        if ($trace) trace_entry();

        $type      = isset($request['type']) ? $request['type'] : null;
        $key       = isset($request['key'])  ? $request['key']  : null;
        $data      = isset($request['data']) ? $request['data'] : null;
        $test_mode = isset($options['test_mode']) ? $options['test_mode'] : 0;
        if (! preg_match('/^[A-Za-z0-9_]+$/', $type)) throw new \Exception('ERROR: the "type" parameter has illegal characters (type may only be the [A-Za-z0-9_] characters)');
        if (! preg_match('/^[ -&\\(-\\[\\]-~]+$/', $key)) throw new \Exception('ERROR: the "key" parameter has illegal characters (key may only be the printable ASCII characters excluding [\'"\\])');
        $response = array('success'=>true);   # assume we are going to succeed, whatever it is we are doing

        if ($type === 'grid') {
            $table        = 'db_view';
            $id_column    = 'view_id';
            $key_column   = 'view_key';
            $label_column = 'view_label';
        }
        elseif ($type === 'chart') {
            $table        = 'db_chart';
            $id_column    = 'chart_id';
            $key_column   = 'chart_key';
            $label_column = 'chart_label';
        }
        else {
            $table        = 'app_store';
            $id_column    = 'store_id';
            $key_column   = 'store_key';
            $type_column  = 'store_type';
            $label_column = 'content_str';
        }

        $metadb = $context->database("meta");

        if (!$test_mode) throw new \Exception('ERROR: the "delete" op is not allowed except if the "test_mode" is set in "options.ini"');

        if ($table === 'app_store') {
            $nrows = $metadb->execute("delete from {meta_schema_}app_store where store_type = '$type' and store_key like '$key'");
        }
        else {
            $nrows = $metadb->execute("delete from {meta_schema_}$table where $key_column like '$key'");
        }

        $response['message'] = "$nrows row(s) deleted";
        $json = json_encode($response);

        if ($trace) trace_exit();
    }
}

